package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import java.util.ResourceBundle;

import model.PersonModel;

public class UserDao {
	private static ResourceBundle rb = ResourceBundle.getBundle("dao.jdbc");
	private static String classname = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String username = rb.getString("username");
	private static String password = rb.getString("password");
   
	static {
		try {
			Class.forName(classname);
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动加载失败");
			e.printStackTrace();
		}

	}

	public static void exClose(ResultSet resultSet, Statement statement, Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	// 获得连接
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, username, password);
			System.out.println("数据库连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		return connection;

	}
	// 插入 单个

	public int  insertSingle(PersonModel person) {

		String sql = "insert into people (username,password,sex,hobby,address) values(?,?,?,?,?)";
		Connection connection = getConnection();
   int num = 0;
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		String username = person.getUsername();
		System.out.println("username=" + username);
		String password = person.getPassword();
		String sex = person.getSex();
		String hobby = person.getHobby();
		String address = person.getAddress();

		int r = 0;
		try {
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setString(3, sex);
			ps.setString(4, hobby);
			ps.setString(5, address);
			num = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(r);

		exClose(null, ps, connection);
  return num;
	}

	// 删除
	public static int delte(PersonModel person) {

		String username = person.getUsername();
		Connection connection = getConnection();
		String sql = "delete from people where username like?";
		PreparedStatement ps = null;
        int num =0 ;
		try {
			ps = connection.prepareStatement(sql);

			ps.setString(1, username);
			num = ps.executeUpdate();

			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);
		return num;

	}

	// 按照账号查询信息
	public static PersonModel selectSingle(PersonModel person) {
		String sql = "select * from people where username=?";

		String username = person.getUsername();

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		PreparedStatement ps = null;

		PersonModel person2 = new PersonModel();
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, username);

			rs = ps.executeQuery();
			while (rs.next()) {
				person2.setId(rs.getInt("id"));
				person2.setUsername(rs.getString("username"));
				person2.setPassword(rs.getString("password"));
				person2.setSex(rs.getString("sex"));
				person2.setHobby(rs.getString("hobby"));
				person2.setAddress(rs.getString("address"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		exClose(rs, ps, connection);

		return person2;

	}
	// 查询所有学生的信息 

	public static ArrayList<PersonModel> selectAll() {
		String sql = "select * from people";

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		ArrayList<PersonModel> persons = new ArrayList<>();

		PreparedStatement ps = null;

		try {
	

			ps = connection.prepareStatement(sql);

			rs = ps.executeQuery();
			

			while (rs.next()) {
				PersonModel person = new PersonModel();
				person.setId(rs.getInt("id"));
				person.setUsername(rs.getString("username"));
				person.setPassword(rs.getString("password"));
				person.setSex(rs.getString("sex"));
				person.setHobby(rs.getString("hobby"));
				person.setAddress(rs.getString("address"));

				persons.add(person);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		exClose(rs, ps, connection);

		return persons;

	}

	// 更新信息
	public static void updateInformation(PersonModel person) {
		Connection connection = getConnection();
        int id = person.getId();
		String username = person.getUsername();
		String sex = person.getSex();
		String hobby = person.getHobby();
		String address = person.getAddress();
		
       System.out.println(username);
       System.out.println(sex);
       System.out.println(hobby);
       System.out.println(address);
		String sql = "update people set username=?,sex=?,hobby=?,address=? where id=?";

		PreparedStatement ps =null;
		try {
			ps =connection.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, sex);
			ps.setString(3, hobby);
			ps.setString(4, address);
			ps.setInt(5, id);
		
			
			ps.executeUpdate();

			System.out.println("操作成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("操作失败");
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

}
